import pymysql
from faker import Faker   #Faker库
import random
import time
from datetime import datetime

#
# class DB:
#     fff=Faker(locale="zh_cn")
#
#     def connect(self):
#         self.db=pymysql.connect(host="120.55.83.187",user="root",password="A123456!",database="zhengkexin",cursorclass=pymysql.cursors.DictCursor)
#         self.cursor=self.db.cursor()   #创建游标
#     def close(self):
#         self.cursor.close()
#         self.db.close()

# 1、通过python连接pymysql数据库，封装类方法
# 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# 1.2、查询全部的数据
# 1.3、删除指定一条数据
# 1.4、封装批量删除方法，可以删除多条
# 1.5、可以指定增加单独一条数据到表里
# 以上每个操作步骤要写日志信息到log.txt里
# class Ins(DB):
#     def insert100(self):
#         self.connect()
#
#         for i in range(100):
#             name=self.fff.name()
#             age=random.randint(18,65)
#             address=self.fff.address()
#             ssn=self.fff.ssn(min_age=18,max_age=65)
#             sql=f"insert into students(name,age,address,ssn)   values('{name}','{age}','{address}','{ssn}')"
#
#             try:
#                 self.cursor.execute(sql)
#                 self.db.commit()
#                 print("插入成功随机100条数据")
#                 with open("log.txt","a",encoding="utf-8") as b :
#                     b.write(f"当前时间：{datetime.now()}：{name},{age},{address},{ssn} 插入成功\n")
#             except:
#                 print(f"{sql}插入失败")
#                 self.db.rollback()
#                 with open("log.txt","a",encoding="utf-8") as b:
#                     b.write(f"当前时间：{datetime.now()}:{name},{age},{address},{ssn} 插入失败\n")
#         self.close()
#
#     def Findall(self):
#         self.connect()
#
#         sql="selext * from students"
#         self.cursor.execute(sql)
#         data=self.cursor.fetchall()
#         for i in data:
#             print(f"当前数据为： {i[name]},{i[age]},{i[address]},{i[ssn]}")
#         self.close()
#
#     def delete(self,name):
#         self.connect()
#
#         sql=f"delete from students where name='{name}'"
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             print(f"删除{name}成功")
#             with open("log.txt", "a", encoding="utf-8") as b:
#                 b.write(f"当前时间：{datetime.now()}：{name}删除成功\n")
#         except:
#             print(f"{sql}删除失败")
#             self.db.rollback()
#             with open("log.txt", "a", encoding="utf-8") as b:
#                 b.write(f"当前时间：{datetime.now()}:{name}删除失败\n")
#         self.close()
#
#     def delete_many(self,*args):
#         self.connect()
#
#         for i in args:
#             sql=f"delete from students where name='{i}'"
#             try:
#                 self.cursor.execute(sql)
#                 self.db.commit()
#                 print(f"批量删除{i}成功")
#                 with open("log.txt", "a", encoding="utf-8") as b:
#                     b.write(f"当前时间：{datetime.now()}：{i}删除成功\n")
#             except:
#                 print(f"{i}批量删除失败")
#                 self.db.rollback()
#                 with open("log.txt", "a", encoding="utf-8") as b:
#                     b.write(f"当前时间：{datetime.now()}:{i}删除失败\n")
#         self.close()
#
#     def insert(self,name,age,address,ssn):
#         self.connect()
#
#         sql=f"insert into students(name,age,address,ssn)  values('{name}','{age}','{address}','{ssn}')"
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             print(f"插入{name}成功")
#             with open("log.txt", "a", encoding="utf-8") as b:
#                 b.write(f"当前时间：{datetime.now()}：{name},{age},{address},{ssn}删除成功\n")
#         except:
#             print(f"{sql}插入一条失败")
#             self.db.rollback()
#             with open("log.txt", "a", encoding="utf-8") as b:
#                 b.write(f"当前时间：{datetime.now()}:{name},{age},{address},{ssn}删除失败\n")
#         self.close()
#
# if __name__ == '__main__':
#     aaa=Ins()
# 增加100条
#     aaa.insert100()
# 查询全部数据
#     aaa.Findall()
# 删除指定一条数据
#     aaa.delete("李波")
# 封装批量删除方法，可以删除多条
#     aaa.delete_many("高秀兰","章婷婷","王刚","何瑜")
# 以上每个操作步骤要写日志信息到log.txt里


# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
# class Ins(DB):
#     def insert(self,name,age,address,phone):
#         self.connect()
#
#         sql=f"insert into student1(name,age,address,phone)  values('{name}','{age}','{address}','{phone}')"
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             print(f"插入一条成功：{name}")
#         except:
#             print(f"{sql}插入一条失败")
#             self.db.rollback()
#         self.close()
#
#     def __edit(self, sql):  # 封装一个主函数
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             self.close()
#         except:
#             self.db.rollback()
#             self.close()
#             print("查询失败")
#
#     def update(self,sql):
#         self.__edit(sql)
#
#
#     def delete(self, name):
#         self.connect()
#         sql=f"delete from student1 where name='{name}'"
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             print(f"删除{name}成功")
#         except:
#             print(f"{sql}删除失败")
#             self.db.rollback()
#         self.close()
#
#     def Findall(self):
#         self.connect()
#         sql="select * from student1"
#         self.cursor.execute(sql)
#         data=self.cursor.fetchall()
#         for i in data:
#             print(f"当前数据为： {i['name']},{i['age']},{i['address']},{i['phone']}")
#         self.close()
#
#     def random_dao(self):
#         self.connect()
#         list1 = []
#         sql="select name from student1; "
#         self.cursor.execute(sql)
#         data=self.cursor.fetchall()   #[{'name': '老六'}, {'name': '老七'}, {'name': '老爸'}]
#         for i in data:
#             list1.append(i['name'])
#         # print(list1)   #['老六', '老七', '老爸']
#         b=random.choice(list1)
#         print(f"{b}到！")





#
# if __name__ == '__main__':
#     aaa=Ins()

# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
#     aaa.insert("李四",18,"北京","123")
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
#     aaa.update("update student1 set name='四四四' where name='李四';")
# 删除学生：删除的学生将不在展示
#     aaa.delete("四四四")
# 展示全部学生：可以看到全部的学生姓名
#     aaa.Findall()
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#     aaa.random_dao()



# 3、类作业，统计镰刀和斧头的数量，要使用类方法
# class Tool:
#     lian_count=0
#     fu_count=0
#     @classmethod
#     def count(a):
#         return {"镰刀":a.lian_count,"斧头":a.fu_count}
#
# class lian(Tool):
#     def __init__(self):
#         Tool.lian_count=Tool.lian_count+1
#
# class fu(Tool):
#     def __init__(self):
#         Tool.fu_count=Tool.fu_count+1
#
# if __name__ == '__main__':
#     aaa=[lian(),lian(),fu()]
#     print(Tool.count())


# 4、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。

# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
# class Ins(DB):
#     def insert100(self):
#         self.connect()
#         for i in range(100):
#             id=i+1
#             name=self.fff.name()
#             english=random.randint(0,100)
#             math=random.randint(0,100)
#             chinese= random.randint(0, 100)
#             class1=random.choice(["1809A","1809B","1810A","1810B"])
#             sql=f"insert into students(id,name,english,math,chinese)   values('{id}','{name}','{english}','{math}','{chinese}')"
#             try:
#                 self.cursor.execute(sql)
#                 self.db.commit()
#                 print("插入成功随机100条数据")
#             except:
#                 print(f"{sql}插入失败")
#                 self.db.rollback()
#         self.close()
#
#     def select_english(self):
#         self.connect()
#         self.cursor.execute("select * from student4 where english between 90 and 100;")
#         data1=self.cursor.fetchall()
#         print(f"优秀的学生信息：{data1}")
#
#         self.cursor.execute("select * from student4 where english between 80 and 90;")
#         data2 = self.cursor.fetchall()
#         print(f"良好的学生信息：{data2}")
#         self.cursor.execute("select * from student4 where english between 70 and 80;")
#         data3= self.cursor.fetchall()
#         print(f"中等的学生信息：{data3}")
#         self.cursor.execute("select * from student4 where english between 60 and 70;")
#         data4 = self.cursor.fetchall()
#         print(f"合格的学生信息：{data4}")
#         self.cursor.execute("select * from student4 where english<60;")
#         data5 = self.cursor.fetchall()
#         print(f"不合格的学生信息：{data5}")
#         self.close()
#
#     def math_score(self):
#         self.connect()
#         self.cursor.execute("select name,class1 from student4 where class1='1809A' order by math desc;")
#         data1=self.cursor.fetchall()
#         print(f"班级1809A的数字成绩情况：{data1}")
#
#         self.cursor.execute("select name,class1 from student4 where class1='1809B' order by math desc;")
#         data2 = self.cursor.fetchall()
#         print(f"班级1809A的数字成绩情况：{data2}")
#         self.cursor.execute("select name,class1 from student4 where class1='1810A' order by math desc;")
#         data3 = self.cursor.fetchall()
#         print(f"班级1809A的数字成绩情况：{data3}")
#         self.cursor.execute("select name,class1 from student4 where class1='1810B' order by math desc;")
#         data4 = self.cursor.fetchall()
#         print(f"班级1809A的数字成绩情况：{data4}")
#         self.close()
#
#     def class1_count(self):
#         self.connect()
#         self.cursor.execute("select count(*),class1 from student4 group by class1 order by count(*) desc; ")
#         self.close()
#
#     def four(self):
#         self.connect()
#         self.cursor.execute("select a.name,a.class1,b.* from student4 a,(select count(*) as num,class1 from student4 where english<60 and math<60 and chinese<60 group by class1) b where a.class1=b.class1 and a.english<60 and a.math<60 and a.chinese<60 order by b.num desc;")
#         data=self.cursor.fetchall()
#         print(f"输出结果：{data}")
#
# if __name__ == '__main__':
    # aaa=Ins()
    # aaa.four()





# 5、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
class Ins:
    def __init__(self,host,user,password,database,cursorclass):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.cursorclass = cursorclass

    def connect(self):
        self.db=pymysql.connect(host=self.host,user=self.user,password=self.password,database=self.database,cursorclass=self.cursorclass)
        self.cursor=self.db.cursor()

    def close(self):
        self.cursor.close()
        self.db.close()

    def Findone(self,sql):
        self.connect()

        try:
            self.cursor.execute(sql)
            result=self.cursor.fetchone()
            self.close()
            return result

        except Exception as e:
            self.db.rollback()
            self.close()
            print(f"查询失败，错误为：{e}")
            return None

    def Findall(self,sql):
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
        except Exception as e:
            self.db.rollback()
        self.close()

    def __edit(self,sql):   #封装一个主函数
        self.connect()
        try:
            self.cursor.execute(sql)
            self.db.commit()    #提交本次改动
        except Exception as e:
            self.db.rollback()
        self.close()

    def insert(self,sql):
        self.__edit(sql)
    def update(self,sql):
        self.__edit(sql)
    def delete(self,sql):
        self.__edit(sql)

    def mo_select(self,a):
        self.connect()
        sql=f"select * from student5 where name like '%{a}%';"
        self.cursor.execute(sql)
        data=self.cursor.fetchall()
        print(f"{data}")



if __name__ == '__main__':
    aaa = Ins(host="120.55.83.187", user="root", password="A123456!", database="zhengkexin",cursorclass=pymysql.cursors.DictCursor)
#新增：图书名、作者名称、新建年月日
    # aaa.insert("insert into student5 values('三国','罗贯中','2025-1-1')")
    # aaa.insert("insert into student5 values('西游记','吴承恩','2023-1-1')")
    # aaa.insert("insert into student5 values('水浒传','施耐庵','2024-1-1')")
    # aaa.insert("insert into student5 values('红楼梦','曹雪芹','2021-1-1')")
# 修改：图书名、作者名称、新建年月日
    #aaa.update("update student5 set name='三国三国三国' where name='三国' ")
# 删除：图书删掉，且不在展示
#     aaa.delete("delete from student5 where name='西游记'")
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
    # 全量查询：
    # print(aaa.Findall("select * from student5"))
    # 精确查询：
    # print(aaa.Findone("select * from student5 where name='红楼梦'"))
    # 模糊查询：
    aaa.mo_select("梦")





































